package com.demo2do.fms.admin.entity.report.report;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.WeakHashMap;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;
import com.demo2do.fms.entity.supplier.common.BusinessType;

/**
 * 过往业务信息查询
 *
 * @author Ark
 *
 */
public class BusinessHistoryReportSearch implements QueryBuilder {

    /** 默认分区 */
    private String branchId;
    
    /** 公司代码 */
    private String code;
    
    /** 公司名称 */
    private String shortName;
    
    /** 审核状态 */
    private List<Integer> status;
    
    /** 供方类型 */
    private String type;
    
    /** 供方级别 */
    private String level;
    
    /** 起始行 */
    private Integer beginIndex;
    
    /** 结束行 */
    private Integer endIndex;
    
    private QueryFilter queryFilter;

    private QueryOrder queryOrder;
    
    /**
     * The default constructor
     */
    public BusinessHistoryReportSearch() {
        queryOrder = new QueryOrder();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if (queryFilter == null || refresh) {
            
            queryFilter = new QueryFilter().addEquals("s", "branch_id", branchId)
                                           .addEquals("s", "code", code)
                                           .addLike("s", "short_name", shortName)
                                           .addEquals("s", "supplier_type", type)
                                           .addEquals("s", "supplier_level", level);
            
        }
        
        return queryFilter;
        
    }
    
    /**
     * @param status
     */
    public void addStatus(List<Integer> status) {
        this.getPreparedFilter(false).addSentence("s.status IN (:status)", "status", status);
    }

    /**
     * build count sentence
     * 
     * @return
     */
    public String buildCountTemplate() {
        String sentence = "SELECT COUNT(*) FROM fms_supplier s, fms_supplier_business_history h WHERE s.is_disabled = 0 AND h.is_disabled = 0 AND s.id = h.supplier_id" + getPreparedFilter(false).clauseSentence();
        return sentence;
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(512);
        
        buffer.append("SELECT s.code AS code, ")
                     .append("s.short_name AS shortName, ")
                     .append("CASE WHEN S.SUPPLIER_TYPE = 0 THEN '运输' WHEN S.SUPPLIER_TYPE = 1 THEN '仓储' END AS supplierType,")
                     .append("CASE WHEN s.status = 0 THEN '备选' WHEN s.status = 1 THEN '待审核' WHEN s.status = 2 THEN '合格' WHEN s.status = 3 THEN '驳回' WHEN s.status = 4 THEN '变更申报' WHEN s.status = 5 THEN '合格但I级单项整改中' WHEN s.status = 6 THEN '合格但II、III级单项整改中' WHEN s.status = 7 THEN '合格但综合整改中' WHEN s.status = 8 THEN '变更驳回' END AS supplierStatus, ")
                     .append("s.business_type AS businessType, ")
                     .append("CASE WHEN s.business_nature = 'MARKET' THEN '市场业务' WHEN s.business_nature = 'INDUSTRY' THEN '安吉体系业内' WHEN s.business_nature = 'OUTDUSTRY' THEN '安吉体系业外' END AS businessNature, ")
                     .append("h.start_date AS startDate, ")
                     .append("h.end_date AS endDate, ")
                     .append("h.consignor AS consignor, ")
                     .append("h.executor AS executor, ")
                     .append("h.brand AS brand, ")
                               
                     .append("h.from_load_place AS fromLoadPlace, ")
                     .append("h.to_load_place AS toLoadPlace, ")
                     .append("h.vehicle_num AS vehicleNum, ")
                     .append("h.average_capacity AS averageCapacity, ")
                     .append("CASE WHEN h.trans_type = 0 THEN '长途' WHEN h.trans_type = 1 THEN '短途' WHEN h.trans_type = 2 THEN '短驳' END AS transType, ")
                               
                     .append("h.service_location AS serviceLocation, ")
                     .append("h.staff_num AS staffNum, ")
                     .append("h.average_traffic AS averageTraffic, ")
                     .append("CASE WHEN h.ware_type = 0 THEN '仓储运作' WHEN h.ware_type = 1 THEN '车辆检查' WHEN h.ware_type = 2 THEN '车辆维护' WHEN h.ware_type = 3 THEN '车辆维修' WHEN h.ware_type = 4 THEN '短驳服务' WHEN h.ware_type = 5 THEN '加改装服务' WHEN h.ware_type = 6 THEN '其他' END AS wareType ")
              .append("FROM fms_supplier s, fms_supplier_business_history h ")
              .append("WHERE s.is_disabled = 0 AND h.is_disabled = 0 AND s.id = h.supplier_id")
              .append(getPreparedFilter(false).clauseSentence() + queryOrder.sentence());
        
        String sentence = "SELECT page.*, ROWNUM rn FROM (" + buffer.toString() + ") page WHERE ROWNUM <= " + endIndex;
        sentence = "SELECT * FROM (" + sentence + ") WHERE rn >= " + beginIndex;
        
        return sentence;
        
    }
    
    /**
     * build rowMapper
     * 
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {
            
            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("code", rs.getObject("code"));
                result.put("shortName", rs.getObject("shortName"));
                result.put("supplierType", rs.getObject("supplierType"));
                result.put("supplierStatus", rs.getObject("supplierStatus"));
                result.put("businessType", getBusinessTypeDescription(rs.getString("businessType")));
                result.put("businessNature", rs.getObject("businessNature"));
                result.put("startDate", rs.getObject("startDate"));
                result.put("endDate", rs.getObject("endDate"));
                result.put("consignor", rs.getObject("consignor"));
                result.put("executor", rs.getObject("executor"));
                result.put("brand", rs.getObject("brand"));
                
                result.put("fromLoadPlace", rs.getObject("fromLoadPlace"));
                result.put("toLoadPlace", rs.getObject("toLoadPlace"));
                result.put("vehicleNum", rs.getBigDecimal("vehicleNum"));
                result.put("averageCapacity", rs.getBigDecimal("averageCapacity"));
                result.put("transType", rs.getObject("transType"));
                
                result.put("serviceLocation", rs.getObject("serviceLocation"));
                result.put("staffNum", rs.getBigDecimal("staffNum"));
                result.put("averageTraffic", rs.getBigDecimal("averageTraffic"));
                result.put("wareType", rs.getObject("wareType"));
                
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }
    
    /**
     * 获取业务类型
     * 
     * @param businessType
     * @return
     */
    public String getBusinessTypeDescription(String businessType) {
        
        if (StringUtils.isBlank(businessType)) {
            return null;
        }
        
        List<String> codes = Arrays.asList(StringUtils.split(businessType, ","));
        List<String> displays = new ArrayList<String>();
        for (String code : codes) {
            BusinessType type = BusinessType.codeOf(code);
            displays.add(type.getDescription());
        }
        
        return StringUtils.join(displays, "、");
        
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return queryOrder;
    }

    /**
     * @return the branchId
     */
    public String getBranchId() {
        return branchId;
    }

    /**
     * @return the code
     */
    public String getCode() {
        return code;
    }

    /**
     * @return
     */
    public String getShortName() {
        return shortName;
    }

    /**
     * @return the status
     */
    public List<Integer> getStatus() {
        return status;
    }

    /**
     * @return the type
     */
    public String getType() {
        return type;
    }

    /**
     * @return the level
     */
    public String getLevel() {
        return level;
    }

    /**
     * @return the beginIndex
     */
    public Integer getBeginIndex() {
        return beginIndex;
    }

    /**
     * @return the endIndex
     */
    public Integer getEndIndex() {
        return endIndex;
    }

    /**
     * @param branchId the branchId to set
     */
    public void setBranchId(String branchId) {
        this.branchId = branchId;
    }

    /**
     * @param code the code to set
     */
    public void setCode(String code) {
        this.code = code;
    }

    /**
     * @param shortName
     */
    public void setShortName(String shortName) {
        this.shortName = shortName;
    }

    /**
     * @param status the status to set
     */
    public void setStatus(List<Integer> status) {
        this.status = status;
    }

    /**
     * @param type the type to set
     */
    public void setType(String type) {
        this.type = type;
    }

    /**
     * @param level the level to set
     */
    public void setLevel(String level) {
        this.level = level;
    }

    /**
     * @param beginIndex the beginIndex to set
     */
    public void setBeginIndex(Integer beginIndex) {
        this.beginIndex = beginIndex;
    }

    /**
     * @param endIndex the endIndex to set
     */
    public void setEndIndex(Integer endIndex) {
        this.endIndex = endIndex;
    }

}